package com.itfreer.xjmh.temporary;


import com.itfreer.form.api.JdbcBaseDaoImp;
import com.itfreer.xjmh.temporary.dao.TemporaryPoliceDao;
import com.itfreer.xjmh.temporary.dao.TemporaryShowDao;
import com.itfreer.xjmh.temporary.entity.TemporaryPoliceEntity;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Component;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Component
public class jdbcTemporaryShowDao extends JdbcBaseDaoImp implements TemporaryShowDao {


    public jdbcTemporaryShowDao(EntityManager entityManager) {
        super(entityManager);
    }

    @Override
    public List<Map<String, Object>> getShowData(String createTime, String airport, String adminId) {
        // 自定义查询
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        String sql = "SELECT\n" +
                "\tadmin.airport AS adminairport,\n" +
                "\tadmin.airport_name AS airportName,\n" +
                "\tadmin.administration_check AS administrationcheck,\n" +
                "\tadmin.hide_danger AS adminhidedanger,\n" +
                "\tadmin.hand_notice_book AS adminhandnoticebook,\n" +
                "\t'' airsendpolice,\n" +
                "\t'' airsendsecurityperson,\n" +
                "\t'' airillegalevent,\n" +
                "\t'' airdisturbevent,\n" +
                "\t'' floorincomeflight,\n" +
                "\t'' flooroutcomeflight,\n" +
                "\t'' floorincomepassenger,\n" +
                "\t'' flooroutcomepassenger,\n" +
                "\t'' floorsendsecurityperson,\n" +
                "\t'' floorcheckpassenger,\n" +
                "\t'' floorcheckbag,\n" +
                "\t'' floorcheckgoods,\n" +
                "\t'' floordangergoods,\n" +
                "\t'' floorguns,\n" +
                "\t'' floorcontrolequipment,\n" +
                "\t'' policesendpolice,\n" +
                "\t'' policepatrolperson,\n" +
                "\t'' policecheckcar,\n" +
                "\t'' policebangoods,\n" +
                "\t'' policesecuritycase,\n" +
                "\t'' policecriminalcase,\n" +
                "\t'' policecriminalperson,\n" +
                "\t'' policeterroristperson,\n" +
                "\t'' policeupperson,\n" +
                "\t'' policeescapeperson,\n" +
                "\t'' policecultperson,\n" +
                "\t'' policehidedanger \n" +
                "FROM\n" +
                "\tt_temp_administration admin\n" +
                "WHERE\n" +
                "\t(admin.s_temporary_time <= '"+ createTime +"' AND admin.s_temporary_time >= '"+ createTime +"' )\n";
                if (airport != null && airport.length() > 0) {
                    sql = sql + "\tAND admin.airport = "+ airport + "\n";
                }
                sql += "UNION ALL\n" +
                "SELECT\n" +
                "\tjc.id AS airport,\n" +
                "\tjc.s_display AS airportName,\n" +
                "\t'' administrationcheck,\n" +
                "\t'' adminhidedanger,\n" +
                "\t'' adminhandnoticebook,\n" +
                "\tair.send_police AS airsendpolice,\n" +
                "\tair.send_security_person AS airsendsecurityperson,\n" +
                "\tair.illegal_event AS airillegalevent,\n" +
                "\tair.disturb_event AS airdisturbevent,\n" +
                "\tfloor.income_flight AS floorincomeflight,\n" +
                "\tfloor.outcome_flight AS flooroutcomeflight,\n" +
                "\tfloor.income_passenger AS floorincomepassenger,\n" +
                "\tfloor.outcome_passenger AS flooroutcomepassenger,\n" +
                "\tfloor.send_security_person AS floorsendsecurityperson,\n" +
                "\tfloor.check_passenger AS floorcheckpassenger,\n" +
                "\tfloor.check_bag AS floorcheckbag,\n" +
                "\tfloor.check_goods AS floorcheckgoods,\n" +
                "\tfloor.danger_goods AS floordangergoods,\n" +
                "\tfloor.guns AS floorguns,\n" +
                "\tfloor.control_equipment AS floorcontrolequipment,\n" +
                "\tpolice.send_police AS policesendpolice,\n" +
                "\tpolice.patrol_person AS policepatrolperson,\n" +
                "\tpolice.check_car AS policecheckcar,\n" +
                "\tpolice.ban_goods AS policebangoods,\n" +
                "\tpolice.security_case AS policesecuritycase,\n" +
                "\tpolice.criminal_case AS policecriminalcase,\n" +
                "\tpolice.criminal_person AS policecriminalperson,\n" +
                "\tpolice.terrorist_person AS policeterroristperson,\n" +
                "\tpolice.up_person AS policeupperson,\n" +
                "\tpolice.escape_person AS policeescapeperson,\n" +
                "\tpolice.cult_person AS policecultperson,\n" +
                "\tpolice.hide_danger AS policehidedanger \n" +
                "FROM\n" +
                "\t( SELECT id, s_display FROM data_dictionary WHERE s_dicname = 'zd_airport' ) jc\n" +
                "\tLEFT JOIN t_temp_air air ON jc.id = air.airport\n" +
                "\tLEFT JOIN t_temp_floor floor ON jc.id = floor.airport\n" +
                "\tLEFT JOIN t_temp_police police ON jc.id = police.airport \n" +
                "WHERE\n" +
                "\t((air.s_temporary_time <= '"+ createTime +"' AND air.s_temporary_time >= '"+ createTime +"' )\n" +
                "\tOR (floor.s_temporary_time <= '"+ createTime +"' AND floor.s_temporary_time >= '"+ createTime +"' )\n" +
                "\tOR (police.s_temporary_time <= '"+ createTime +"' AND police.s_temporary_time >= '"+ createTime +"' ))\n";
                if (airport != null && airport.length() > 0){
                    sql += "\tAND jc.id = "+ airport +"\n";
                }
                sql += "GROUP BY\n" +
                "\tairport";

        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        list = query.getResultList();
        return list;
    }

    @Override
    public List<Map<String, Object>> getShowDate() {
        // 自定义查询
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        String sql = "SELECT date,\n" +
                "\t SUM(administrationcheck) AS administrationcheck,\n" +
                "\t SUM(adminhidedanger) AS adminhidedanger,\n" +
                "\t SUM(adminhandnoticebook) AS adminhandnoticebook,\n" +
                "\t SUM(airsendpolice) AS airsendpolice,\n" +
                "\t SUM(airsendsecurityperson) AS airsendsecurityperson,\n" +
                "\t SUM(airillegalevent) AS airillegalevent,\n" +
                "\t SUM(airdisturbevent) AS airdisturbevent,\n" +
                "\t SUM(floorincomeflight) AS floorincomeflight,\n" +
                "\t SUM(flooroutcomeflight) AS flooroutcomeflight,\n" +
                "\t SUM(floorincomepassenger) AS floorincomepassenger,\n" +
                "\t SUM(flooroutcomepassenger) AS flooroutcomepassenger,\n" +
                "\t SUM(floorsendsecurityperson) AS floorsendsecurityperson,\n" +
                "\t SUM(floorcheckpassenger) AS floorcheckpassenger,\n" +
                "\t SUM(floorcheckbag) AS floorcheckbag,\n" +
                "\t SUM(floorcheckgoods) AS floorcheckgoods,\n" +
                "\t SUM(floordangergoods) AS floordangergoods,\n" +
                "\t SUM(floorguns) AS floorguns,\n" +
                "\t SUM(floorcontrolequipment) AS floorcontrolequipment,\n" +
                "\t SUM(policesendpolice) AS policesendpolice,\n" +
                "\t SUM(policepatrolperson) AS policepatrolperson,\n" +
                "\t SUM(policecheckcar) AS policecheckcar,\n" +
                "\t SUM(policebangoods) AS policebangoods,\n" +
                "\t SUM(policesecuritycase) AS policesecuritycase,\n" +
                "\t SUM(policecriminalcase) AS policecriminalcase,\n" +
                "\t SUM(policecriminalperson) AS policecriminalperson,\n" +
                "\t SUM(policeterroristperson) AS policeterroristperson,\n" +
                "\t SUM(policeupperson) AS policeupperson,\n" +
                "\t SUM(policeescapeperson) AS policeescapeperson,\n" +
                "\t SUM(policecultperson) AS policecultperson ,\n" +
                "\t SUM(policehidedanger) AS policehidedanger \n" +
                "\tFROM (SELECT\n" +
                "\tDATE_FORMAT(s_temporary_time,\"%Y/%m/%d\") as date,\n" +
                "\tSUM(admin.administration_check) AS administrationcheck,\n" +
                "\tSUM(admin.hide_danger) AS adminhidedanger,\n" +
                "\tSUM(admin.hand_notice_book) AS adminhandnoticebook,\n" +
                "\t'' airsendpolice,\n" +
                "\t'' airsendsecurityperson,\n" +
                "\t'' airillegalevent,\n" +
                "\t'' airdisturbevent,\n" +
                "\t'' floorincomeflight,\n" +
                "\t'' flooroutcomeflight,\n" +
                "\t'' floorincomepassenger,\n" +
                "\t'' flooroutcomepassenger,\n" +
                "\t'' floorsendsecurityperson,\n" +
                "\t'' floorcheckpassenger,\n" +
                "\t'' floorcheckbag,\n" +
                "\t'' floorcheckgoods,\n" +
                "\t'' floordangergoods,\n" +
                "\t'' floorguns,\n" +
                "\t'' floorcontrolequipment,\n" +
                "\t'' policesendpolice,\n" +
                "\t'' policepatrolperson,\n" +
                "\t'' policecheckcar,\n" +
                "\t'' policebangoods,\n" +
                "\t'' policesecuritycase,\n" +
                "\t'' policecriminalcase,\n" +
                "\t'' policecriminalperson,\n" +
                "\t'' policeterroristperson,\n" +
                "\t'' policeupperson,\n" +
                "\t'' policeescapeperson,\n" +
                "\t'' policecultperson,\n" +
                "\t'' policehidedanger \n" +
                "FROM\n" +
                "\tt_temp_administration admin \n" +
                "GROUP BY DATE_FORMAT(admin.s_temporary_time,\"%Y/%m/%d\") \n" +
                "UNION ALL\n" +
                "SELECT\n" +
                "\tDATE_FORMAT(s_temporary_time,\"%Y/%m/%d\") as date ,\n" +
                "\t'' administrationcheck,\n" +
                "\t'' hidedanger,\n" +
                "\t'' handnoticebook,\n" +
                "\tSUM(air.send_police) AS airsendpolice,\n" +
                "\tSUM(air.send_security_person) AS airsendsecurityperson,\n" +
                "\tSUM(air.illegal_event) AS airillegalevent,\n" +
                "\tSUM(air.disturb_event) AS airdisturbevent,\n" +
                "\t'' floorincomeflight,\n" +
                "\t'' flooroutcomeflight,\n" +
                "\t'' floorincomepassenger,\n" +
                "\t'' flooroutcomepassenger,\n" +
                "\t'' floorsendsecurityperson,\n" +
                "\t'' floorcheckpassenger,\n" +
                "\t'' floorcheckbag,\n" +
                "\t'' floorcheckgoods,\n" +
                "\t'' floordangergoods,\n" +
                "\t'' floorguns,\n" +
                "\t'' floorcontrolequipment,\n" +
                "\t'' policesendpolice,\n" +
                "\t'' policepatrolperson,\n" +
                "\t'' policecheckcar,\n" +
                "\t'' policebangoods,\n" +
                "\t'' policesecuritycase,\n" +
                "\t'' policecriminalcase,\n" +
                "\t'' policecriminalperson,\n" +
                "\t'' policeterroristperson,\n" +
                "\t'' policeupperson,\n" +
                "\t'' policeescapeperson,\n" +
                "\t'' policecultperson,\n" +
                "\t'' policehidedanger \n" +
                "FROM t_temp_air air \n" +
                "GROUP BY DATE_FORMAT(air.s_temporary_time,\"%Y/%m/%d\") \n" +
                "UNION ALL\n" +
                "SELECT\n" +
                "\tDATE_FORMAT(s_temporary_time,\"%Y/%m/%d\") as date ,\n" +
                "\t'' administrationcheck,\n" +
                "\t'' hidedanger,\n" +
                "\t'' handnoticebook,\n" +
                "\t'' airsendpolice,\n" +
                "\t'' airsendsecurityperson,\n" +
                "\t'' airillegalevent,\n" +
                "\t'' airdisturbevent,\n" +
                "\tSUM(floor.income_flight) AS floorincomeflight,\n" +
                "\tSUM(floor.outcome_flight) AS flooroutcomeflight,\n" +
                "\tSUM(floor.income_passenger) AS floorincomepassenger,\n" +
                "\tSUM(floor.outcome_passenger) AS flooroutcomepassenger,\n" +
                "\tSUM(floor.send_security_person) AS floorsendsecurityperson,\n" +
                "\tSUM(floor.check_passenger) AS floorcheckpassenger,\n" +
                "\tSUM(floor.check_bag) AS floorcheckbag,\n" +
                "\tSUM(floor.check_goods) AS floorcheckgoods,\n" +
                "\tSUM(floor.danger_goods) AS floordangergoods,\n" +
                "\tSUM(floor.guns) AS floorguns,\n" +
                "\tSUM(floor.control_equipment) AS floorcontrolequipment,\n" +
                "\t'' policesendpolice,\n" +
                "\t'' policepatrolperson,\n" +
                "\t'' policecheckcar,\n" +
                "\t'' policebangoods,\n" +
                "\t'' policesecuritycase,\n" +
                "\t'' policecriminalcase,\n" +
                "\t'' policecriminalperson,\n" +
                "\t'' policeterroristperson,\n" +
                "\t'' policeupperson,\n" +
                "\t'' policeescapeperson,\n" +
                "\t'' policecultperson,\n" +
                "\t'' policehidedanger \n" +
                "FROM t_temp_floor floor \n" +
                "GROUP BY DATE_FORMAT(floor.s_temporary_time,\"%Y/%m/%d\") \n" +
                "UNION ALL\n" +
                "SELECT\n" +
                "\tDATE_FORMAT(s_temporary_time,\"%Y/%m/%d\") as date ,\n" +
                "\t'' administrationcheck,\n" +
                "\t'' hidedanger,\n" +
                "\t'' handnoticebook,\n" +
                "\t'' airsendpolice,\n" +
                "\t'' airsendsecurityperson,\n" +
                "\t'' airillegalevent,\n" +
                "\t'' airdisturbevent,\n" +
                "\t'' floorincomeflight,\n" +
                "\t'' flooroutcomeflight,\n" +
                "\t'' floorincomepassenger,\n" +
                "\t'' flooroutcomepassenger,\n" +
                "\t'' floorsendsecurityperson,\n" +
                "\t'' floorcheckpassenger,\n" +
                "\t'' floorcheckbag,\n" +
                "\t'' floorcheckgoods,\n" +
                "\t'' floordangergoods,\n" +
                "\t'' floorguns,\n" +
                "\t'' floorcontrolequipment,\n" +
                "\tSUM(police.send_police) AS policesendpolice,\n" +
                "\tSUM(police.patrol_person) AS policepatrolperson,\n" +
                "\tSUM(police.check_car) AS policecheckcar,\n" +
                "\tSUM(police.ban_goods) AS policebangoods,\n" +
                "\tSUM(police.security_case) AS policesecuritycase,\n" +
                "\tSUM(police.criminal_case) AS policecriminalcase,\n" +
                "\tSUM(police.criminal_person) AS policecriminalperson,\n" +
                "\tSUM(police.terrorist_person) AS policeterroristperson,\n" +
                "\tSUM(police.up_person) AS policeupperson,\n" +
                "\tSUM(police.escape_person) AS policeescapeperson,\n" +
                "\tSUM(police.cult_person) AS policecultperson,\n" +
                "\tSUM(police.hide_danger) AS policehidedanger \n" +
                "FROM t_temp_police police \n" +
                "GROUP BY DATE_FORMAT(police.s_temporary_time,\"%Y/%m/%d\") ) t\n" +
                "GROUP BY t.date\n" +
                "ORDER BY t.date";

        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        list = query.getResultList();
        return list;
    }
}
